﻿using System;
using System.Collections.Generic;
using System.Linq;
using Model;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class CarrierListRepository
    {
        // 添加载具清单
        public int AddCarrierList(CarrierList carrierList)
        {
            string sql = @"INSERT INTO carrierlist 
                            (CarrierId, BatchId, DurableSpecId) 
                           VALUES 
                            (@CarrierId, @BatchId, @DurableSpecId)";

            MySqlParameter[] parameters = {
                new MySqlParameter("@CarrierId", carrierList.CarrierId),
                new MySqlParameter("@BatchId", carrierList.BatchId),
                new MySqlParameter("@DurableSpecId", carrierList.DurableSpecId)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 删除载具清单
        public int DeleteCarrierList(int carrierListId)
        {
            string sql = "DELETE FROM carrierlist WHERE CarrierListId = @CarrierListId";
            MySqlParameter[] parameters = { new MySqlParameter("@CarrierListId", carrierListId) };
            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 更新载具清单信息
        public int UpdateCarrierList(CarrierList carrierList)
        {
            string sql = @"UPDATE carrierlist 
                            SET CarrierId = @CarrierId, 
                                BatchId = @BatchId, 
                                DurableSpecId = @DurableSpecId 
                          WHERE CarrierListId = @CarrierListId";

            MySqlParameter[] parameters = {
                new MySqlParameter("@CarrierId", carrierList.CarrierId),
                new MySqlParameter("@BatchId", carrierList.BatchId),
                new MySqlParameter("@DurableSpecId", carrierList.DurableSpecId),
                new MySqlParameter("@CarrierListId", carrierList.CarrierListId)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 查询所有载具清单
        public List<CarrierList> GetAllCarrierLists()
        {
            string sql = "SELECT * FROM carrierlist";
            return DBHelper.ExecuteQuery(sql, null, MapCarrierList);
        }

        // 查询单个载具清单
        public CarrierList GetCarrierListById(int carrierListId)
        {
            string sql = "SELECT * FROM carrierlist WHERE CarrierListId = @CarrierListId";
            MySqlParameter[] parameters = { new MySqlParameter("@CarrierListId", carrierListId) };
            var carrierLists = DBHelper.ExecuteQuery(sql, parameters, MapCarrierList);
            return carrierLists.FirstOrDefault();
        }

        // 根据六个参数查询载具清单，返回所有列的数据
        // 根据六个参数查询载具清单，返回所有列的数据
        public List<Dictionary<string, object>> SearchCarrierLists(
            string detailedCarrierType = null,
            string capacityStatus = null,
            string cleaningStatus = null,
            string carrierStatus = null,
            int? carrierId = null,
            int? durableSpecId = null)
        {
            string sql = @"

                SELECT 
                    cl.*,
                    c.*,
                    ds.*
                FROM CarrierList cl
                INNER JOIN Carrier c ON cl.CarrierId = c.CarrierId
                INNER JOIN DurableSpec ds ON cl.DurableSpecId = ds.DurableSpecId
                WHERE 1=1";

            List<MySqlParameter> parameters = new List<MySqlParameter>();

            // 根据参数构建查询条件
            if (!string.IsNullOrEmpty(detailedCarrierType))
            {
                sql += " AND c.DetailedCarrierType = @DetailedCarrierType";
                parameters.Add(new MySqlParameter("@DetailedCarrierType", detailedCarrierType));
            }

            if (!string.IsNullOrEmpty(capacityStatus))
            {
                sql += " AND c.CapacityStatus = @CapacityStatus";
                parameters.Add(new MySqlParameter("@CapacityStatus", capacityStatus));
            }

            if (!string.IsNullOrEmpty(cleaningStatus))
            {
                sql += " AND c.CleaningStatus = @CleaningStatus";
                parameters.Add(new MySqlParameter("@CleaningStatus", cleaningStatus));
            }

            if (!string.IsNullOrEmpty(carrierStatus))
            {
                sql += " AND c.CarrierStatus = @CarrierStatus";
                parameters.Add(new MySqlParameter("@CarrierStatus", carrierStatus));
            }

            if (carrierId.HasValue)
            {
                sql += " AND c.CarrierId = @CarrierId";
                parameters.Add(new MySqlParameter("@CarrierId", carrierId.Value));
            }

            if (durableSpecId.HasValue)
            {
                sql += " AND ds.DurableSpecId = @DurableSpecId";
                parameters.Add(new MySqlParameter("@DurableSpecId", durableSpecId.Value));
            }

            // 使用 DBHelper.ExecuteQuery 方法执行查询并直接返回结果
            return DBHelper.ExecuteQuery(sql, parameters.ToArray(), reader =>
            {
                var row = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[reader.GetName(i)] = reader[i];
                }
                return row;
            });
        }
        // 映射数据库查询结果到 CarrierList 对象
        private CarrierList MapCarrierList(MySqlDataReader reader)
        {
            return new CarrierList
            {
                CarrierListId = (int)reader["CarrierListId"],
                CarrierId = (int)reader["CarrierId"],
                BatchId = (int)reader["BatchId"],
                DurableSpecId = (int)reader["DurableSpecId"]
            };
        }
    }
}